package com.pillpoppers.pillbox.database;

import java.util.ArrayList;

import com.pillpoppers.pillbox.Contact;
import com.pillpoppers.pillbox.Schedule;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class PillboxDB 
{
	// Database constants
    public static final String DB_NAME = "pillbox.db";
    public static final int    DB_VERSION = 1;

    // Schedule table constants
    public static final String SCHEDULE_TABLE = "schedule";
    
    public static final String SCHEDULE_ID = "schedule_id";
    public static final int    SCHEDULE_ID_COL = 0;
    
    public static final String SCHEDULE_ACTIVE = "active";
    public static final int    SCHEDULE_ACTIVE_COL = 1;
    
    public static final String SCHEDULE_HOUR = "hour";
    public static final int    SCHEDULE_HOUR_COL = 2;
    
    public static final String SCHEDULE_MINUTE = "minute";
    public static final int    SCHEDULE_MINUTE_COL = 3;
    
    public static final String SCHEDULE_DAY = "day";
    public static final int    SCHEDULE_DAY_COL = 4;
    
    public static final String SCHEDULE_ALARM_BY = "alarmBy";
    public static final int    SCHEDULE_ALARM_BY_COL = 5;
    
    public static final String SCHEDULE_PILL_IDS = "pillIds";
    public static final int    SCHEDULE_PILL_IDS_COL = 6;
    
    // Contact table constants
    
    public static final String CONTACT_TABLE = "contact";
    
    public static final String CONTACT_ID = "contact_id";
    public static final int    CONTACT_ID_COL = 0;
    
    public static final String CONTACT_NAME = "name";
    public static final int    CONTACT_NAME_COL = 1;
    
    public static final String CONTACT_PHONE = "phone";
    public static final int    CONTACT_PHONE_COL = 2;
    
    public static final String CONTACT_EMAIL = "email";
    public static final int    CONTACT_EMAIL_COL = 3;
    
    public static final String CONTACT_OCCUPATION = "occupation";
    public static final int    CONTACT_OCCUPATION_COL = 4;
    
    // CREATE and DROP TABLE statements
    public static final String CREATE_SCHEDULE_TABLE = 
            "CREATE TABLE " + SCHEDULE_TABLE + " (" + 
            SCHEDULE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
            SCHEDULE_ACTIVE + " INTEGER, " +
            SCHEDULE_HOUR + " INTEGER, " +
            SCHEDULE_MINUTE + " INTEGER, " +
            SCHEDULE_DAY + " INTEGER, " +
            SCHEDULE_ALARM_BY + " INTEGER, " +
            SCHEDULE_PILL_IDS + " TEXT)";
    
    public static final String CREATE_CONTACT_TABLE = 
            "CREATE TABLE " + CONTACT_TABLE + " (" + 
            CONTACT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
            CONTACT_NAME + " TEXT, " +
            CONTACT_PHONE + " TEXT, " +
            CONTACT_EMAIL + " TEXT, " +
            CONTACT_OCCUPATION + " TEXT)";

    public static final String DROP_SCHEDULE_TABLE = 
            "DROP TABLE IF EXISTS " + SCHEDULE_TABLE;
    
    public static final String DROP_CONTACT_TABLE = 
            "DROP TABLE IF EXISTS " + CONTACT_TABLE;
    
    private static class DBHelper extends SQLiteOpenHelper
    {

        public DBHelper(Context context, String name, 
                CursorFactory factory, int version)
        {
            super(context, name, factory, version);
        }

        @Override
        public void onCreate(SQLiteDatabase db)
        {
            // Create tables
            db.execSQL(CREATE_SCHEDULE_TABLE);
            db.execSQL(CREATE_CONTACT_TABLE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, 
                int oldVersion, int newVersion)
        {

            Log.d("Pillbox", "Upgrading db from version " 
                    + oldVersion + " to " + newVersion);
            
            Log.d("Pillbox", "Deleting all data!");
            db.execSQL(PillboxDB.DROP_SCHEDULE_TABLE);
            db.execSQL(PillboxDB.DROP_CONTACT_TABLE);
            onCreate(db);
        }
    } // end DBHelper
    
    // database object and database helper object
    private SQLiteDatabase db;
    private DBHelper dbHelper;
    
    // Constructor
    public PillboxDB(Context context)
    {
        dbHelper = new DBHelper(context, DB_NAME, null, DB_VERSION);
    }
    
    // Private methods
    private void openReadableDB()
    {
        db = dbHelper.getReadableDatabase();
    }
    
    private void openWriteableDB()
    {
        db = dbHelper.getWritableDatabase();
    }
    
    private void closeDB()
    {
        if (db != null)
            db.close();
    }
    
    // Public methods
    
    //=============================================================================================
    // Schedule functions
    //=============================================================================================
    
    /**
     * Gets all values from schedule table.
     * @return ArrayList<Schedule> with all values from schedule database
     */
    public ArrayList<Schedule> getSchedules()
    {
    	ArrayList<Schedule> sl = new ArrayList<Schedule>();
    	
    	this.openReadableDB();
        Cursor cursor = db.query(SCHEDULE_TABLE, null, 
                null, null, null, null, "day, hour, minute");        
        while (cursor.moveToNext()) {
             sl.add(getScheduleFromCursor(cursor));
        }
        if (cursor != null)
            cursor.close();        
        this.closeDB();
    	
    	return sl;
    }
    
    /**
     * Gets schedule with specified ID.
     * @param id : ID of Schedule object to retrieve
     * @return Schedule object corresponding to id, empty Schedule if not found
     */
    public Schedule getSchedule(long id)
    {	
    	String where = SCHEDULE_ID + "= ?";
        String[] whereArgs = { Long.toString(id) };
        Schedule schedule;
        
        this.openReadableDB();        
        Cursor cursor = db.query(SCHEDULE_TABLE, 
                null, where, whereArgs, null, null, null);
        cursor.moveToFirst();
        schedule = getScheduleFromCursor(cursor);
        if (cursor != null)
            cursor.close();
        this.closeDB();
    	
    	return schedule;
    }
    
    /**
     * Gets Schedule based on day, hour, minute.
     * @param day : day of Schedule object to retrieve
     * @param hour : hour of Schedule object to retrieve
     * @param minute : day of Schedule object to retrieve
     * @return Schedule specified or empty schedule if failed
     */
    public Schedule getSchedule(int day, int hour, int minute)
    {	
    	String where = SCHEDULE_DAY  + "= " + day + " AND " + 
                       SCHEDULE_HOUR + "= " + hour + " AND " +
    			       SCHEDULE_MINUTE + "= " + minute;
        
        Schedule schedule;
        
        this.openReadableDB();        
        Cursor cursor = db.query(SCHEDULE_TABLE, 
                null, where, null, null, null, null);
        cursor.moveToFirst();
        schedule = getScheduleFromCursor(cursor);
        if (cursor != null)
            cursor.close();
        this.closeDB();
    	
    	return schedule;
    }
    
    /**
     * Gets schedule data from cursor position in database.
     * @param cursor : Cursor that points to a position in database
     * @return Schedule object at cursor position
     */
    private static Schedule getScheduleFromCursor(Cursor cursor)
    {
        if (cursor == null || cursor.getCount() == 0)
        {
            return null;
        }
        else
        {
            try
            {
                String str = "";
            	String[] strArr;
            	ArrayList<Integer> li = new ArrayList<Integer>();
            	Schedule schedule = new Schedule();
            	
                schedule.setScheduleId(cursor.getInt(SCHEDULE_ID_COL));
                if (cursor.getInt(SCHEDULE_ACTIVE_COL) == 0)
                {
                	schedule.setActive(false);
                }
                else
                {
                	schedule.setActive(true);
                }
                schedule.setHour(cursor.getInt(SCHEDULE_HOUR_COL));
                schedule.setMinute(cursor.getInt(SCHEDULE_MINUTE_COL));
                schedule.setDayRepeat(cursor.getInt(SCHEDULE_DAY_COL));
                schedule.setAlarmBy(cursor.getInt(SCHEDULE_ALARM_BY_COL));
                str = cursor.getString(SCHEDULE_PILL_IDS_COL);
                strArr = str.split(":");
                for (int i = 0; i < strArr.length; i++)
                {
                	li.add(Integer.parseInt(strArr[i]));
                }
                
                schedule.setPillIds(li);
                
                
                return schedule;
            }
            catch(Exception e)
            {
                return null;
            }
        }
    }
    
    /**
     * Inserts a schedule object into the database.
     * @param schedule : Schedule object to be inserted into database
     * @return rowID of inserted schedule, -1 if there was an error
     */
    public long insertSchedule(Schedule schedule)
    {
    	StringBuilder sb = new StringBuilder();
    	ArrayList<Integer> il = new ArrayList<Integer>();
    	ContentValues cv = new ContentValues();
    	
        cv.put(SCHEDULE_ID, schedule.getScheduleId());
        if (schedule.isActive())
        {
        	cv.put(SCHEDULE_ACTIVE, 1);
        }
        else
        {
        	cv.put(SCHEDULE_ACTIVE, 0);
        }
        cv.put(SCHEDULE_HOUR, schedule.getHour());
        cv.put(SCHEDULE_MINUTE, schedule.getMinute());
        cv.put(SCHEDULE_DAY, schedule.getDayOfWeek());
        cv.put(SCHEDULE_ALARM_BY, schedule.getAlarmBy());
        il = schedule.getPillIds();
        for (int i = 0; i < il.size(); i++)
        {
        	if (i != il.size() - 1)
        		sb.append(il.get(i) + ":");
        	else
        		sb.append(il.get(i));
        }
        cv.put(SCHEDULE_PILL_IDS, sb.toString());
        
        this.openWriteableDB();
        long rowID = db.insert(SCHEDULE_TABLE, null, cv);
        this.closeDB();
        
        return rowID;
    }
    
    /**
     * Updates given Schedule object in database 
     * @param schedule : Schedule object to be updated in database
     * @return Number of rows updated
     */
    public int updateSchedule(Schedule schedule)
    {
    	StringBuilder sb = new StringBuilder();
    	ArrayList<Integer> il = new ArrayList<Integer>();
    	ContentValues cv = new ContentValues();
    	
    	cv.put(SCHEDULE_ID, schedule.getScheduleId());
        if (schedule.isActive())
        {
        	cv.put(SCHEDULE_ACTIVE, 1);
        }
        else
        {
        	cv.put(SCHEDULE_ACTIVE, 0);
        }
        cv.put(SCHEDULE_HOUR, schedule.getHour());
        cv.put(SCHEDULE_MINUTE, schedule.getMinute());
        cv.put(SCHEDULE_DAY, schedule.getDayOfWeek());
        cv.put(SCHEDULE_ALARM_BY, schedule.getAlarmBy());
        il = schedule.getPillIds();
        for (int i = 0; i < il.size(); i++)
        {
        	if (i != il.size() - 1)
        		sb.append(il.get(i) + ":");
        	else
        		sb.append(il.get(i));
        }
        cv.put(SCHEDULE_PILL_IDS, sb.toString());
        
        String where = SCHEDULE_ID + "= ?";
        String[] whereArgs = { String.valueOf(schedule.getScheduleId()) };

        this.openWriteableDB();
        int rowCount = db.update(SCHEDULE_TABLE, cv, where, whereArgs);
        this.closeDB();
        return rowCount;
    }
    
    /**
     * Deletes specified schedule from database.
     * @param id : The ID of the schedule to be deleted from database
     * @return The number of rows deleted
     */
    public int deleteSchedule(long id)
    {
    	String where = SCHEDULE_ID + "= ?";
        String[] whereArgs = { String.valueOf(id) };

        this.openWriteableDB();
        int rowCount = db.delete(SCHEDULE_TABLE, where, whereArgs);
        this.closeDB();
        
        return rowCount;
    }
    
    //=============================================================================================
    // Contact functions
    //=============================================================================================
    
    /**
     * Gets all values from contact table.
     * @return ArrayList<Contact> with all values from contact database
     */
    public ArrayList<Contact> getContacts()
    {
    	ArrayList<Contact> cl = new ArrayList<Contact>();
    	
    	this.openReadableDB();
        Cursor cursor = db.query(CONTACT_TABLE, null, 
                null, null, null, null, null);        
        while (cursor.moveToNext()) {
             cl.add(getContactFromCursor(cursor));
        }
        if (cursor != null)
            cursor.close();        
        this.closeDB();
    	
    	return cl;
    }
    
    /**
     * Gets contact with specified ID.
     * @param id : ID of Contact object to retrieve
     * @return Contact object corresponding to id, null Contact if not found
     */
    public Contact getContact(long id)
    {	
    	String where = CONTACT_ID + "= ?";
        String[] whereArgs = { Long.toString(id) };
        Contact contact;
        
        this.openReadableDB();        
        Cursor cursor = db.query(CONTACT_TABLE, 
                null, where, whereArgs, null, null, null);
        cursor.moveToFirst();
        contact = getContactFromCursor(cursor);
        if (cursor != null)
            cursor.close();
        this.closeDB();
    	
    	return contact;
    }
    
    /**
     * Gets contact data from cursor position in database.
     * @param cursor : Cursor that points to a position in database
     * @return Contact object at cursor position
     */
    private static Contact getContactFromCursor(Cursor cursor)
    {
        if (cursor == null || cursor.getCount() == 0)
        {
            return null;
        }
        else
        {
            try
            {
            	Contact contact = new Contact();
            	
                contact.setContactID(cursor.getInt(SCHEDULE_ID_COL));
                contact.setName(cursor.getString(CONTACT_NAME_COL));
                contact.setPhone(cursor.getString(CONTACT_PHONE_COL));
                contact.setEmail(cursor.getString(CONTACT_EMAIL_COL));
                contact.setOccupation(cursor.getString(CONTACT_OCCUPATION_COL));
                
                return contact;
            }
            catch(Exception e)
            {
                return null;
            }
        }
    }
    
    /**
     * Inserts a Contact object into the database.
     * @param contact : Contact object to be inserted into database
     * @return rowID of inserted contact, -1 if there was an error
     */
    public long insertContact(Contact contact)
    {
    	ContentValues cv = new ContentValues();
    	
        cv.put(CONTACT_ID, contact.getContactID());
        cv.put(CONTACT_NAME, contact.getName());
        cv.put(CONTACT_PHONE, contact.getPhone());
        cv.put(CONTACT_EMAIL, contact.getEmail());
        cv.put(CONTACT_OCCUPATION, contact.getOccupation());
        
        this.openWriteableDB();
        long rowID = db.insert(CONTACT_TABLE, null, cv);
        this.closeDB();
        
        return rowID;
    }
    
    /**
     * Updates given Contact object in database 
     * @param contact : Contact object to be updated in database
     * @return Number of rows updated
     */
    public int updateContact(Contact contact)
    {
    	ContentValues cv = new ContentValues();
    	
    	cv.put(CONTACT_ID, contact.getContactID());
        cv.put(CONTACT_NAME, contact.getName());
        cv.put(CONTACT_PHONE, contact.getPhone());
        cv.put(CONTACT_EMAIL, contact.getEmail());
        cv.put(CONTACT_OCCUPATION, contact.getOccupation());
        
        String where = CONTACT_ID + "= ?";
        String[] whereArgs = { String.valueOf(contact.getContactID()) };

        this.openWriteableDB();
        int rowCount = db.update(CONTACT_TABLE, cv, where, whereArgs);
        this.closeDB();
        return rowCount;
    }
    
    /**
     * Deletes specified contact from database.
     * @param id : The ID of the contact to be deleted from database
     * @return The number of rows deleted
     */
    public int deleteContact(long id)
    {
    	String where = CONTACT_ID + "= ?";
        String[] whereArgs = { String.valueOf(id) };

        this.openWriteableDB();
        int rowCount = db.delete(CONTACT_TABLE, where, whereArgs);
        this.closeDB();
        
        return rowCount;
    }
    
}